IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveSrvrInstncCfg1MSSQL' AND type = 'P')
     DROP PROCEDURE dbo.SaveSrvrInstncCfg1MSSQL
GO

CREATE PROCEDURE dbo.SaveSrvrInstncCfg1MSSQL
     @SrvrInstncId               int,
     @PackageExecutionId         bigint,
     @ClusteredFlag              tinyint,
     @FullTextInstalledFlag      tinyint,
     @IntegratedSecurityOnlyFlag tinyint,
     @ProductVersion             varchar(30),
     @ProductLevel               varchar(30),
     @ServerEdition              varchar(128),
     @LicenseType                varchar(30),
     @NumberOfLicenses           int,
     @SrvrCollation              varchar(128),
     @SrvrMinMemoryMB            int,
     @SrvrMaxMemoryMB            int
AS
/*
 *
 * SaveSrvrInstncCfg1MSSQL
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 06/06/2009, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @NOW     datetime,
             @StartDt datetime

     -- Get current date/time 
     SELECT @NOW = CURRENT_TIMESTAMP 
     
     -- Generate place to hold current record
     DECLARE @CurrRec TABLE ( SrvrInstncId               int          NOT NULL,
                              PackageExecutionId         bigint       NOT NULL,
                              ClusteredFlag              tinyint          NULL,
                              FullTextInstalledFlag      tinyint          NULL,
                              IntegratedSecurityOnlyFlag tinyint          NULL,
                              ProductVersion             varchar(30)      NULL,
                              ProductLevel               varchar(30)      NULL,
                              ServerEdition              varchar(128)     NULL,
                              LicenseType                varchar(30)      NULL,
                              NumberOfLicenses           int              NULL,
                              SrvrCollation              varchar(128)     NULL,
                              SrvrMinMemoryMB            int              NULL,
                              SrvrMaxMemoryMB            int              NULL )

     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.SrvrInstncCfg1MSSQL
                     WHERE  SrvrInstncId = @SrvrInstncId )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          INSERT dbo.SrvrInstncCfg1MSSQL
               ( SrvrInstncId,
                 PackageExecutionId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 ClusteredFlag,
                 FullTextInstalledFlag,
                 IntegratedSecurityOnlyFlag,
                 ProductVersion,
                 ProductLevel,
                 ServerEdition,
                 LicenseType,
                 NumberOfLicenses,
                 SrvrCollation,
                 SrvrMinMemoryMB,
                 SrvrMaxMemoryMB )
          SELECT @SrvrInstncId,
                 @PackageExecutionId,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @ClusteredFlag,
                 @FullTextInstalledFlag,
                 @IntegratedSecurityOnlyFlag,
                 @ProductVersion,
                 @ProductLevel,
                 @ServerEdition,
                 @LicenseType,
                 @NumberOfLicenses,
                 @SrvrCollation,
                 @SrvrMinMemoryMB,
                 @SrvrMaxMemoryMB
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the startdt for the current record, if we are here one should
          -- be available, we perform a little trick here as well, taking 
          -- advantage or the ORDER BY  and making sure we have the maximum
          -- StartDt
          SELECT @StartDt = StartDt
          FROM   dbo.SrvrInstncCfg1MSSQL
          WHERE  SrvrInstncId          = @SrvrInstncId
          AND    CurrentRecordFlag     = 1
          ORDER BY StartDt

          -- Reset current record flag to zero and set the end date to now minus
          -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
          -- not handle a difference less than 3ms.  Therefore it ends right before
          -- the new record starts with no overlap.
          UPDATE dbo.SrvrInstncCfg1MSSQL
          SET    EndDt             = DATEADD( ms, -3, @NOW ),
                 CurrentRecordFlag = 0
          WHERE  SrvrInstncId      = @SrvrInstncId
          AND    CurrentRecordFlag = 1

          -- We do the insert and use the last current record to fill in any NULLs
          -- that we might have gotten in this run, we don't want to lose anything
          -- that we already have.  We use COALESCE for this.
          INSERT dbo.SrvrInstncCfg1MSSQL
               ( SrvrInstncId,
                 PackageExecutionId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 ClusteredFlag,
                 FullTextInstalledFlag,
                 IntegratedSecurityOnlyFlag,
                 ProductVersion,
                 ProductLevel,
                 ServerEdition,
                 LicenseType,
                 NumberOfLicenses,
                 SrvrCollation,
                 SrvrMinMemoryMB,
                 SrvrMaxMemoryMB )
          SELECT @SrvrInstncId,
                 @PackageExecutionId,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 COALESCE( @ClusteredFlag,              ClusteredFlag ),
                 COALESCE( @FullTextInstalledFlag,      FullTextInstalledFlag ),
                 COALESCE( @IntegratedSecurityOnlyFlag, IntegratedSecurityOnlyFlag ),
                 COALESCE( @ProductVersion,             ProductVersion ),
                 COALESCE( @ProductLevel,               ProductLevel ),
                 COALESCE( @ServerEdition,              ServerEdition ),
                 COALESCE( @LicenseType,                LicenseType ),
                 COALESCE( @NumberOfLicenses,           NumberOfLicenses ),
                 COALESCE( @SrvrCollation,              SrvrCollation ),
                 COALESCE( @SrvrMinMemoryMB,            SrvrMinMemoryMB ),
                 COALESCE( @SrvrMaxMemoryMB,            SrvrMaxMemoryMB )
          FROM   dbo.SrvrInstncCfg1MSSQL
          -- This is all kind of long, we don't want to write a record that has no 
          -- changes, but due to the NULLs possibilities we have to do a couple of
          -- comparisons, the first is that they don't equal, this is a simple check
          -- that assumes that neither the current record has a NULL or the COALESCE
          -- statement generates a NULL, in the cases that this happens the case will
          -- test as neither true OR false and will effectively be ignored, this is 
          -- ok for our purposes.  So in the second case we look to see if the current
          -- record is NULL for the field and if so whether the COALESCE'd fields are 
          -- NOT NULL, if they are we want to write a new record, this is the equivalent
          -- of getting update information where we didn't have it before.
          WHERE  SrvrInstncId = @SrvrInstncId
          AND    StartDt      = @StartDt
          AND  ( ( ClusteredFlag              != COALESCE( @ClusteredFlag,              ClusteredFlag )
                OR FullTextInstalledFlag      != COALESCE( @FullTextInstalledFlag,      FullTextInstalledFlag )
                OR IntegratedSecurityOnlyFlag != COALESCE( @IntegratedSecurityOnlyFlag, IntegratedSecurityOnlyFlag )
                OR ProductVersion             != COALESCE( @ProductVersion,             ProductVersion )
                OR ProductLevel               != COALESCE( @ProductLevel,               ProductLevel )
                OR ServerEdition              != COALESCE( @ServerEdition,              ServerEdition )
                OR LicenseType                != COALESCE( @LicenseType,                LicenseType )
                OR NumberOfLicenses           != COALESCE( @NumberOfLicenses,           NumberOfLicenses )
                OR SrvrCollation              != COALESCE( @SrvrCollation,              SrvrCollation )
                OR SrvrMinMemoryMB            != COALESCE( @SrvrMinMemoryMB,            SrvrMinMemoryMB )
                OR SrvrMaxMemoryMB            != COALESCE( @SrvrMaxMemoryMB,            SrvrMaxMemoryMB )
                 ) 
              OR ( ( ClusteredFlag              IS NULL 
                 AND COALESCE( @ClusteredFlag,              ClusteredFlag )              IS NOT NULL )
                OR ( FullTextInstalledFlag      IS NULL 
                 AND COALESCE( @FullTextInstalledFlag,      FullTextInstalledFlag )      IS NOT NULL )
                OR ( IntegratedSecurityOnlyFlag IS NULL 
                 AND COALESCE( @IntegratedSecurityOnlyFlag, IntegratedSecurityOnlyFlag ) IS NOT NULL )
                OR ( ProductVersion             IS NULL 
                 AND COALESCE( @ProductVersion,             ProductVersion )             IS NOT NULL )
                OR ( ProductLevel               IS NULL 
                 AND COALESCE( @ProductLevel,               ProductLevel )               IS NOT NULL )
                OR ( ServerEdition              IS NULL 
                 AND COALESCE( @ServerEdition,              ServerEdition )              IS NOT NULL )
                OR ( LicenseType                IS NULL 
                 AND COALESCE( @LicenseType,                LicenseType )                IS NOT NULL )
                OR ( NumberOfLicenses           IS NULL 
                 AND COALESCE( @NumberOfLicenses,           NumberOfLicenses )           IS NOT NULL )
                OR ( SrvrCollation              IS NULL 
                 AND COALESCE( @SrvrCollation,              SrvrCollation )              IS NOT NULL )
                OR ( SrvrMinMemoryMB            IS NULL 
                 AND COALESCE( @SrvrMinMemoryMB,            SrvrMinMemoryMB )            IS NOT NULL )
                OR ( SrvrMaxMemoryMB            IS NULL 
                 AND COALESCE( @SrvrMaxMemoryMB,            SrvrMaxMemoryMB )            IS NOT NULL )
                 )
               ) 

          -- Determine outcome.
          IF ( @@ROWCOUNT = 1 )
          BEGIN -- Record inserted, COMMIT it..
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- Record not inserted, do rollback, this isn't an error..
                -- If nothing is different we don't add the record but we also don't 
                -- want to have the current record updates kept either.
               ROLLBACK TRANSACTION
          END
     END
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Save a server instance config MS SQL specific record if anything has changed from the previous run, calls to this sproc will NOT always generate a new record.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveSrvrInstncCfg1MSSQL;
GO
